package com.cenxi.ipdemo1.utils.excel;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

import java.math.BigInteger;
import java.util.*;
import java.util.stream.Collectors;

public class CustomColorStyleStrategy implements CellWriteHandler, SheetWriteHandler {

    private final Map<Integer, List<Integer>> LACK_CARD;
    private final Map<Integer, List<Integer>> LACK_CARD1;
    private final Map<Integer, List<Integer>> FONT_COLOR;
    private final Integer ROW_COUNTS;

    public CustomColorStyleStrategy(Map<Integer, List<Integer>> lackCard, Map<Integer, List<Integer>> lackCard1, Map<Integer, List<Integer>> fontColor, Integer rowCounts) {
        this.LACK_CARD = lackCard;
        this.LACK_CARD1 = lackCard1;
        this.FONT_COLOR = fontColor;
        this.ROW_COUNTS = rowCounts;
    }

    /**
     * 背景颜色
     */
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Boolean head = context.getHead();
        if (!head) {
            // 设置单元格样式
            createStyle(context.getWriteWorkbookHolder().getWorkbook());
            Cell cell = context.getCell();
            int rowIndex = cell.getRowIndex();
            int columnIndex = cell.getColumnIndex();

            //设置单元格背景颜色
            if (!org.springframework.util.CollectionUtils.isEmpty(LACK_CARD)) {
                LACK_CARD.keySet().forEach(a -> {
                    if (rowIndex == a) {
                        List<Integer> integerList = LACK_CARD.get(a);
                        integerList.forEach(c -> {
                            if (columnIndex == c) {
                                WriteCellData<?> cellData = context.getFirstCellData();
                                WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
                                writeCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                                writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                            }
                        });
                    }
                });
            }

            //设置单元格背景颜色
            if (!org.springframework.util.CollectionUtils.isEmpty(LACK_CARD1)) {
                LACK_CARD1.keySet().forEach(a -> {
                    if (rowIndex == a) {
                        List<Integer> integerList = LACK_CARD1.get(a);
                        integerList.forEach(c -> {
                            if (columnIndex == c) {
                                WriteCellData<?> cellData = context.getFirstCellData();
                                WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
                                writeCellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.getIndex());
                                writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
                            }
                        });
                    }
                });
            }

            //设置单元格字体颜色
            if (!org.springframework.util.CollectionUtils.isEmpty(FONT_COLOR)) {
                FONT_COLOR.keySet().forEach(a -> {
                    if (rowIndex == a) {
                        List<Integer> integerList = FONT_COLOR.get(a);
                        integerList.forEach(c -> {
                            if (columnIndex == c) {
                                WriteCellData<?> cellData = context.getFirstCellData();
                                WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
                                WriteFont writeFont = new WriteFont();
                                writeFont.setColor(IndexedColors.LIGHT_BLUE.index);
                                writeCellStyle.setWriteFont(writeFont);
                            }
                        });
                    }
                });
            }
        }
    }

    /**
     * 动态下拉菜单(测试)
     */
    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        // 设置列
        Map<Integer, List<String>> dropCellMap = new HashMap<>();
        dropCellMap.put(9, Arrays.asList("测试", "测四1", "测四2"));
        // 设置行
        List<Integer> rows = new ArrayList<>();
        rows.add(6);
        rows.add(11);
        dropCellMap.forEach((k, v) -> {
            for (Integer row : rows) {
                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(row, row, k, k);
                DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v.toArray(new String[0]));
                DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);

                //设置约束
                if(dataValidation instanceof HSSFDataValidation) {
                    dataValidation.setSuppressDropDownArrow(false);
                } else {
                    dataValidation.setSuppressDropDownArrow(true);
                    dataValidation.setShowErrorBox(true);
                }
                // 禁止输入非下拉框的内容
                dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                dataValidation.createErrorBox("提示", "此值与单元格下拉不符");
                context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
            }
        });
    }

    /**
     * 设置文本格式(测试)
     */
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        List<Integer> columnNumList = new ArrayList<>();
        columnNumList.add(6);
        List<Integer> rows = new ArrayList<>();
        rows.add(6);
        rows.add(11);
        Sheet sheet = writeSheetHolder.getSheet();
        if (columnNumList.contains(cell.getColumnIndex())) {
            for (Integer row : rows) {
                // --- 数据有效性 只允许输入整数 ---
                DataValidationHelper helper = sheet.getDataValidationHelper();
                //校验数值格式 只能输入整数0 -xx 范围内
                DataValidationConstraint constraintNum = new XSSFDataValidationConstraint(
                        DataValidationConstraint.ValidationType.INTEGER,
                        DataValidationConstraint.OperatorType.BETWEEN, "1", "9999");

                CellRangeAddressList regionNumber = new CellRangeAddressList(row, row, columnNumList.get(0), columnNumList.get(columnNumList.size() - 1));
                DataValidation validationNum = helper.createValidation(constraintNum, regionNumber);
                //输入错误提示
                validationNum.createErrorBox("输入值错误", "请输入1-9999之间的数字");
                validationNum.setShowErrorBox(true);
                sheet.addValidationData(validationNum);
            }
        }
    }


    /**
     * 实际中如果直接获取原单元格的样式进行修改, 最后发现是改了整行的样式, 因此这里是新建一个样* 式
     */
    private void createStyle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        // 下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // 左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // 上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        // 右边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        // 水平对齐方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //cellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        // 垂直对齐方式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 单元格边框颜色
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
    }
}
